using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using database;

/// <summary>
/// This class is for performing SQL queries that the xsd is not capable of.
/// </summary>
public class inventory
{
    public inventory()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    database.database db = new database.database();

    public int GetInventory(string strTagSet, int totalTags)
    {
        string query = @"select sum(instock) As Expr1 
                            from inventory where upc in
                                (SELECT b.upc
                                FROM upctagged bt, inventory b, tags t
                                WHERE bt.tag_idx = t.tags_idx
                                AND (t.tags_idx IN (" + strTagSet + @"))
                                AND b.inventory_idx = bt.upc_idx
                                GROUP BY b.inventory_idx
                                HAVING COUNT( b.inventory_idx )=" + totalTags + ")";

        DataSet ds = db.SelectRows(db.connection(),query);

        int Result = Convert.ToInt32(ds.Tables[0].Rows[0].ItemArray[0]);

        return Result;
    }

    public string GetTagSetByUPC(string UPC)
    {
        string result = "";
        string query = @"select tags_name from 
	                        tags t, upctagged ut, inventory i
                        where 
	                        t.tags_idx = ut.tag_idx
                        AND
	                        i.inventory_idx = ut.upc_idx
                        AND
	                        i.upc = '" + UPC + "'";

        DataSet ds = db.SelectRows(db.connection(), query);

        for (int i=0; i<ds.Tables[0].Rows.Count; i++)
        {
            result = result + ds.Tables[0].Rows[i].ItemArray[0].ToString() + "+";
        }
        result = result.Substring(0, result.Length - 1);
        return result;
    }

}